package templateExecel2.utils;


import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

public class PoiUtils {


    /**
     * 根据源Sheet样式copy新Sheet
     */

    //使用文件输入流创建一个 Excel 文件
    // XSSFWorkbook wb = new XSSFWorkbook(fis);
    public static void copySheet(XSSFSheet fromsheet, XSSFSheet newsheet) {

        try {
            if (fromsheet != null && newsheet != null) {
                //设置打印参数
                newsheet.setMargin(HSSFSheet.TopMargin, fromsheet.getMargin(HSSFSheet.TopMargin));// 页边距（上）
                newsheet.setMargin(HSSFSheet.BottomMargin, fromsheet.getMargin(HSSFSheet.BottomMargin));// 页边距（下）
                newsheet.setMargin(HSSFSheet.LeftMargin, fromsheet.getMargin(HSSFSheet.LeftMargin));// 页边距（左）
                newsheet.setMargin(HSSFSheet.RightMargin, fromsheet.getMargin(HSSFSheet.RightMargin));// 页边距（右
                XSSFPrintSetup ps = newsheet.getPrintSetup();
                ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)
                ps.setVResolution((short) 600);
                ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //处理合并的单元格
    public static void handleMergedRegions(XSSFSheet fromsheet, XSSFSheet newsheet) {
        int firstrow = fromsheet.getFirstRowNum();
        int lastrow = fromsheet.getLastRowNum();

        //拷贝合并的单元格
        for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
            CellRangeAddress region = fromsheet.getMergedRegion(i);
            if ((region.getFirstRow() >= firstrow) && (region.getLastRow() <= lastrow)) {
                newsheet.addMergedRegion(region);
            }
        }
    }

    //处理列的宽度
    public static void handleColumnWidth(XSSFSheet fromsheet, XSSFSheet newsheet) {
        int firstrow = fromsheet.getFirstRowNum();
        int lastrow = fromsheet.getLastRowNum();
        for (int i = firstrow; i <= lastrow; i++) {
            XSSFRow fromRow = fromsheet.getRow(i);
            if (fromRow != null) {
                for (int j = fromRow.getLastCellNum() + 1; j >= fromRow.getFirstCellNum(); j--) {
                    int colnum = fromsheet.getColumnWidth((short) j);
                    if (colnum > 100) {
                        newsheet.setColumnWidth((short) j, (short) colnum);
                    }
                    if (colnum == 0) {
                        newsheet.setColumnHidden((short) j, true);
                    } else {
                        newsheet.setColumnHidden((short) j, false);
                    }
                }
                break;
            }
        }
    }

    private static List emptyList = Collections.emptyList();


    /*
        自动添加跨行
     */
    public static void copySheetDataWithData(XSSFSheet fromsheet, XSSFSheet newsheet, Map<Integer, List<Map<Integer, Object>>> data) {

        if (data == null) {
            data = emptyMap;
        }

        int lastrow = fromsheet.getLastRowNum();

        int currentLine = 0;


        List<CellRangeAddress> addresses = fromsheet.getMergedRegions();
        List<CellRangeAddress> mergedRegions = newsheet.getMergedRegions();
        for (int i = 0; i < mergedRegions.size(); i++) {
            newsheet.removeMergedRegion(0);
        }

     /*   for (CellRangeAddress address : addresses) {
            System.out.println(address.getFirstRow() + "->" + address.getLastRow());
        }
*/

        //并没有起到去重的效果
        List<CellRangeAddress> cellRangeAddresses = new ArrayList<>();

        Map<Integer, List<CellRangeAddress>> templateCellRangeAddress = handleTemplateCellRangeAddress(mergedRegions);

        //上一行的合并
        List<CellRangeAddress> preList = emptyList;

        //上一行的数据
        Map<Integer, Object> pre = null;

        // 拷贝行并填充数据
        for (int i = 0; i <= lastrow; i++) {//当切换数据源时,null值有必要进行处理吗?
            preList = emptyList;
            pre = null;

            XSSFRow fromRow = fromsheet.getRow(i);
            List<Map<Integer, Object>> maps = data.get(i);

            //不需要延伸或平移
            if (maps == null || maps.size() == 0) {
                currentLine++;

                //
                preList = emptyList;
                pre = null;

                //如果模板行存在就只复制样式
                if (fromRow != null) {
                    XSSFRow row = newsheet.createRow(currentLine);
                    copyStyle(fromRow, row, 0, fromRow.getLastCellNum());
                }

                continue;
            }

            //首先找到当前行列

            //不需要延伸或平移
            Map<Integer, Object> map = maps.get(0);

            //首行判断有误???
            int firstRow = currentLine;
            int row = currentLine++;

            //找到模板行
            List<CellRangeAddress> template = templateCellRangeAddress.get(i);
            if (template == null) {
                template = emptyList;
            }

            XSSFRow newRow = newsheet.createRow(row);
            preList = copyRowWithDataOrMerge(fromRow, newRow, map, pre, template, preList, true);

            for (CellRangeAddress cellAddresses : preList) {//相同为什么还是能够添加进去????
                if (!cellRangeAddresses.contains(cellAddresses)) {
                    cellRangeAddresses.add(cellAddresses);
                }
            }
            pre = map;

            //需要延伸或平移
            for (int j = 1; j < maps.size(); j++) {
                map = maps.get(j);

                moveOrStretch(preList, currentLine);
                moveOrStretch(templateCellRangeAddress, currentLine);
                row = currentLine++;
                newRow = newsheet.createRow(row);
                preList = copyRowWithDataOrMerge(fromRow, newRow, map, pre, template, preList, false);

                for (CellRangeAddress cellAddresses : preList) {//相同为什么还是能够添加进去????
                    if (!cellRangeAddresses.contains(cellAddresses)) {
                        cellRangeAddresses.add(cellAddresses);
                    }
                }


                pre = map;
            }
        }

        //TODO 模板行不会添加进所有元素内
        //遍历所有跨行单元
        for (CellRangeAddress address : cellRangeAddresses) {
            newsheet.addMergedRegion(address);
        }
    }


    //精确匹配左上角
    private static Map<Integer, List<CellRangeAddress>> handleTemplateCellRangeAddress(List<CellRangeAddress> all) {

        Map<Integer, List<CellRangeAddress>> map = new HashMap<>();

        for (CellRangeAddress cellAddresses : all) {

            //第一行
            int firstRow = cellAddresses.getFirstRow();

            //最后一行
            int lastRow = cellAddresses.getLastRow();
            for (int i = firstRow; i <= lastRow; i++) {
                List<CellRangeAddress> addresses = map.computeIfAbsent(i, k -> new ArrayList<>());
                addresses.add(cellAddresses);
            }
        }

        for (List<CellRangeAddress> value : map.values()) {
            value.sort((a, b) -> {
                if (a.getFirstColumn() > b.getFirstRow()) {
                    return 1;
                } else if (a.getFirstRow() < b.getFirstRow()) {
                    return -1;
                }
                return 0;
            });
        }

        return map;

    }

    /*
        对现有单元格,采用拓展的方式
     */
    private static void moveOrStretch(List<CellRangeAddress> addresses, int row) {

        for (CellRangeAddress address : addresses) {
            if (address.getLastRow() >= row) {
                address.setLastRow(address.getLastRow() + 1);
                if (address.getFirstRow() > row) {
                    address.setFirstRow(address.getFirstRow() + 1);
                }
            }
        }
    }

    private static void moveOrStretch(Map<Integer, List<CellRangeAddress>> map, int row) {

        map.forEach((r, l) -> {
            if (r >= row) {
                moveOrStretch(l, row);
            }
        });

    }


    /*

        模板行的合并单元格是不往目标sheet页中添加的,而是根据模板行进行复制

     */
    private static List<CellRangeAddress> copyRowWithDataOrMerge(XSSFRow fromRow, XSSFRow newRow, Map<Integer, Object> data, Map<Integer, Object> pre, List<CellRangeAddress> templateRowCellRangeAddress, List<CellRangeAddress> preRowCellRangeAddress, boolean newTemplateLine) {

        //设置行高
        newRow.setHeight(fromRow.getHeight());

        if (data == null) {
            data = emptyMap;
        }

        if (pre == null) {
            pre = emptyMap;
        }

        //本行中所有的跨行元素
        List<CellRangeAddress> r = new LinkedList<>();

        //查找上一行时索引

        CellRangeAddressIndex templateIndex = new CellRangeAddressIndex();
        CellRangeAddressIndex preIndex = new CellRangeAddressIndex();

        //遍历本行中所有列  j
        for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {

            XSSFCell fromCell = fromRow.getCell((short) j);

            if (fromCell == null) {
                continue;
            }

            Object cellData = data.get(j);

            //进行特殊处理
            if (cellData == null) {

                //单元格合并问题

                continue;
            }

            // System.out.println(cellData);

            //判断是否有必要与上一行相同列的元素进行合并
            if (pre.get(j) == cellData) {//如果合并

                CellRangeAddress address = null;

                //从上一行中查找是否已经存在了合并单元
                if (preRowCellRangeAddress.size() > 0) {
                    findCellRangeAddressByCol(preRowCellRangeAddress, j, preIndex);
                    address = preIndex.cellAddresses;
                }

                if (address == null) {//不存在合并单元

                    //第二行
                    address = new CellRangeAddress(newRow.getRowNum() - 1, newRow.getRowNum(), j, j);

                    //  System.out.println("值相同,没有在上一行找到需要的合并单元格,创建:" + cellData);

                    //添加进被返回的结果中
                } else {//如果能够找到

                    //   System.out.println("值相同,可以在上一行中找到合并:" + cellData);

                    //拓展跨行
                    if (address.getLastRow() < newRow.getRowNum()) {
                        address.setLastRow(newRow.getRowNum());
                    }


                    copyStyle(fromRow, newRow, j, address.getLastColumn());

                    //跳转指针

                    //TODO 跳转指针 首先进行样式复制
                    j = address.getLastColumn();

                }

                //将找到的或生成的跨行添加进去
                r.add(address);
            } else {//如果没有出现单元格合并状况


                //copyCellWithData(fromCell, newRow.createCell((short) j), cellData);


                //如果是新的换行模板
              /*  if (newTemplateLine) {

                } else {

                }*/


                findCellRangeAddressByCol(templateRowCellRangeAddress, j, templateIndex);
                CellRangeAddress cellAddresses = templateIndex.cellAddresses;
                //如果模板行索引存在
                if (cellAddresses != null) {//如果已经能够在上一行中找到??

                    findCellRangeAddressByCol(preRowCellRangeAddress, j, preIndex);

                    if (preIndex.cellAddresses == null) {
                        //依据模板行单元格合并创建本行的单元格合并
                        CellRangeAddress address = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum() + cellAddresses.getLastRow() - cellAddresses.getFirstRow(), cellAddresses.getFirstColumn(), cellAddresses.getLastColumn());
                        r.add(address);

                        //System.out.println("值不同,但是在模板行中存在单元格合并,但是未在上一行合并中找到合并单元" + cellData);

                    } else {

                        //这里应该添加企换行标记
                        System.out.println("值不同,但是在模板行中存在单元格合并,在上一行中找到单元格合并" + cellData);

                        if (preIndex.cellAddresses.getLastRow() < newRow.getRowNum()) {
                            preIndex.cellAddresses.setLastRow(newRow.getRowNum());
                        }

                        r.add(preIndex.cellAddresses);
                    }

                    copyStyle(fromRow, newRow, j, cellAddresses.getLastColumn());

                    //TODO 跳转指针
                    j = cellAddresses.getLastColumn();

                }
            }

        }

        return r;

    }

    private static void copyStyle(XSSFRow fromRow, XSSFRow newRow, int from, int to) {

        //样式并没有复制完整
        for (int i = from; i <= to; i++) {

            XSSFCell fromCell = fromRow.getCell(i);
            if (fromCell == null) {
                continue;
            }
            XSSFCell newCell = newRow.getCell(i);
            if (newCell == null) {
                newCell = newRow.createCell(i);
            }

            XSSFCellStyle cellStyle = (XSSFCellStyle) fromCell.getCellStyle().clone();
            XSSFCellStyle style = newCell.getCellStyle();

            try {
                field.set(cellStyle, field.get(style));
            } catch (Exception e) {
                e.printStackTrace();
            }

            newCell.setCellStyle(cellStyle);

        }
    }

    /*
        使用列查找对应元素
     */
    private static void findCellRangeAddressByCol(List<CellRangeAddress> cellRangeAddresses, int col, CellRangeAddressIndex index) {

        index.cellAddresses = null;

        //从给定的位置开始
        for (int i = index.index; i < cellRangeAddresses.size(); i++) {
            CellRangeAddress address = cellRangeAddresses.get(i);
            if (address == null) {
                continue;
            }

            index.index = i;
            //表明已经超出了给定边界
            if (address.getFirstColumn() > col) {
                break;
            } else if (address.getLastColumn() >= col) {
                index.cellAddresses = address;
                break;
            }
        }
    }

    private static class CellRangeAddressIndex {
        private CellRangeAddress cellAddresses;
        private int index = 0;
    }


/*    private static CellRangeAddress findCellRangeAddressByCol(List<CellRangeAddress> cellRangeAddresses, int row, int col) {
        for (CellRangeAddress address : cellRangeAddresses) {
            if ((address.getLastColumn() >= col && col >= address.getFirstColumn()) && (address.getLastRow() >= row && row >= address.getFirstRow())) {
                return address;
            }
        }
        return null;
    }*/

    //赋值一样自定义新行中的数据
    public static void copyRowWithData(XSSFRow fromRow, XSSFRow newRow, Map<Integer, Object> data) {

        if (data == null) {
            data = emptyMap;
        }

        newRow.setHeight(fromRow.getHeight());
        for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
            XSSFCell fromCell = fromRow.getCell((short) j);
            if (fromCell == null) {
                continue;
            }
            //copyCellWithData(fromCell, newRow.createCell((short) j), data.get(j));
        }
    }


    //TODO  需要手动处理单元格合并以及列宽
    /*  //处理单元格合并
        handleMergedRegions(fromsheet, newsheet);

        //处理列宽
        handleColumnWidth(fromsheet, newsheet);
*/

    public static void copySheetData(XSSFSheet fromsheet, XSSFSheet newsheet) {
        int lastrow = fromsheet.getLastRowNum();
        // 拷贝行并填充数据
        for (int i = 0; i <= lastrow; i++) {
            XSSFRow fromRow = fromsheet.getRow(i);
            XSSFRow newRow = newsheet.createRow(i);
            if (fromRow == null) {
                continue;
            }

            //TODO i - firstrow  不去除空白行
            copyRow(fromRow, newRow);
        }
    }


    private static Map emptyMap = new HashMap();


    private static Field field;

    static {
        Class c = XSSFCellStyle.class;
        try {
            field = c.getDeclaredField("_stylesSource");
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        field.setAccessible(true);
    }

    public static void copyRow(XSSFRow fromRow, XSSFRow newRow) {
        newRow.setHeight(fromRow.getHeight());
        for (int j = fromRow.getFirstCellNum(); j <= fromRow.getLastCellNum(); j++) {
            XSSFCell fromCell = fromRow.getCell((short) j);
            if (fromCell == null) {
                continue;
            }
            copyCell(fromCell, newRow.createCell((short) j));
        }
    }


    public static void copyCell(XSSFCell fromCell, XSSFCell newCell) {

        XSSFCellStyle cellStyle = (XSSFCellStyle) fromCell.getCellStyle().clone();
        XSSFCellStyle style = newCell.getCellStyle();
        try {
            field.set(cellStyle, field.get(style));
        } catch (Exception e) {
            e.printStackTrace();
        }
        newCell.setCellStyle(cellStyle);


        CellType cType = fromCell.getCellType();
        newCell.setCellType(cType);
        switch (cType) {
            case STRING:
                newCell.setCellValue(fromCell.getRichStringCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(fromCell.getCellFormula());
                break;
            case BOOLEAN:
                newCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellValue(fromCell.getErrorCellValue());
                break;
            default:
                newCell.setCellValue(fromCell.getRichStringCellValue());
                break;
        }
    }

    //读取行列
    public static Map<Integer, Map<Integer, Object>> readSheet(XSSFSheet sheet) {

        Map<Integer, Map<Integer, Object>> map = new HashMap<>();

        int lastRowNum = sheet.getLastRowNum();
        int firstRowNum = sheet.getFirstRowNum();

        for (int i = firstRowNum; i <= lastRowNum; i++) {

            XSSFRow row = sheet.getRow(i);

            if (row == null) {
                continue;
            }
            map.computeIfAbsent(i, key -> readRow(row));

        }
        return map;
    }


    public static Map<Integer, Object> readRow(XSSFRow row) {

        Map<Integer, Object> r = new HashMap<>();
        short firstCellNum = row.getFirstCellNum();
        int lastCellNum = row.getLastCellNum();
        for (int j = firstCellNum; j < lastCellNum; j++) {
            XSSFCell fromCell = row.getCell(j);
            if (fromCell == null) {
                continue;
            }
            r.put(j, readCell(fromCell));
        }

        return r;
    }

    public static Object readCell(XSSFCell fromCell) {
        if (fromCell.getCellType() == CellType.STRING) {
            return fromCell.getStringCellValue();
        } else if (fromCell.getCellType() == CellType.NUMERIC) {
            return fromCell.getNumericCellValue();
        } else if (fromCell.getCellType() == CellType.BOOLEAN) {
            return fromCell.getBooleanCellValue();
        } else if (fromCell.getCellType() == CellType.ERROR) {
            return fromCell.getErrorCellString();
        } else if (fromCell.getCellType() == CellType.FORMULA) {
            return fromCell.getCellFormula();
        }
        return null;
    }


    public static XSSFWorkbook read(InputStream inputStream) throws IOException {
        return new XSSFWorkbook(inputStream);
    }


    public static XSSFWorkbook read(File input) throws IOException {
        FileInputStream inputStream = new FileInputStream(input);
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        inputStream.close();
        return sheets;
    }


    public static void write(XSSFWorkbook wb, OutputStream outputStream) throws IOException {
        wb.write(outputStream);
    }


    public static void write(XSSFWorkbook wb, File out) throws IOException {
        FileOutputStream fileOutputStream = new FileOutputStream(out);
        write(wb, fileOutputStream);
        fileOutputStream.close();
    }


}